﻿using Dapper;
using Domain.Entity.System_setting;
using Domain.IRepository.System_setting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;

namespace EFCore.Repository.System_setting
{
    [Dependency(ServiceLifetime.Scoped)]
    public class DepartRepository : IDepartRepository
    {

        private MyDbContext myDbContext { get; set; }

        public DepartRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public List<Ment_depart> GetDepart()
        {
            string sql = "SELECT * FROM dbo.Info_company WHERE Is_del=0";
            List<Info_company> list = myDbContext.Database.GetDbConnection().Query<Info_company>(sql).ToList();
            List<Ment_depart> list2 = new List<Ment_depart>();
            foreach (Info_company company in list)
            {
                Ment_depart ment_Depart = new Ment_depart()
                {
                    Depart_id = 0,
                    Depart_code = company.Comp_code,
                    Depart_name = company.Comp_name,
                    Depart_remark = company.Comp_Remark,
                    Create_by = company.Create_by,
                    Create_time = company.Create_time,
                    Comp_id = company.Comp_id,
                };

                sql = $"SELECT * FROM dbo.Ment_depart WHERE Comp_id={ment_Depart.Comp_id} and Depart_pid=0 and Is_del=0 ORDER BY Num_order ASC";
                List<Ment_depart> list3 = myDbContext.Database.GetDbConnection().Query<Ment_depart>(sql).ToList();
                List<Ment_depart> list4 = new List<Ment_depart>();
                foreach (Ment_depart depart in list3)
                {
                    Ment_depart depart2 = new Ment_depart()
                    {
                        Depart_id = depart.Depart_id,
                        Depart_code = depart.Depart_code,
                        Depart_name = depart.Depart_name,
                        Depart_remark = depart.Depart_remark,
                        Create_by = depart.Create_by,
                        Create_time = depart.Create_time,
                        Comp_id = depart.Comp_id,
                        Depart_pid = depart.Depart_pid,
                        Depart_status = depart.Depart_status,
                    };
                    depart2.children = GetDepartChildren(ment_Depart.Comp_id, depart2.Depart_id);
                    list4.Add(depart2);
                }
                list3 = list4;
                ment_Depart.children = list3;
                list2.Add(ment_Depart);
            }
            return list2;
        }

        public List<Ment_depart> GetDepartChildren(long id, long Depart_id)
        {
            string sql = $"SELECT * FROM dbo.Ment_depart WHERE Comp_id={id} and Depart_pid={Depart_id} and Is_del=0 ORDER BY Num_order ASC";
            List<Ment_depart> list3 = myDbContext.Database.GetDbConnection().Query<Ment_depart>(sql).ToList();
            List<Ment_depart> tempList = new List<Ment_depart>();
            foreach (Ment_depart depart3 in list3)
            {
                Ment_depart depart4 = new Ment_depart()
                {
                    Depart_id = depart3.Depart_id,
                    Depart_code = depart3.Depart_code,
                    Depart_name = depart3.Depart_name,
                    Depart_remark = depart3.Depart_remark,
                    Create_by = depart3.Create_by,
                    Create_time = depart3.Create_time,
                    Comp_id = depart3.Comp_id,
                    Depart_pid = depart3.Depart_pid,
                    Depart_status = depart3.Depart_status,
                };
                if (GetDepartChildren(id, depart4.Depart_id).Count != 0)
                {
                    depart4.children = GetDepartChildren(id, depart4.Depart_id);
                }
                tempList.Add(depart4); // 将修改后的数据添加到临时集合中
            }
            return tempList;
        }

        public int AddDepart(Ment_depart depart)
        {
            string sql = $@"INSERT INTO dbo.Ment_depart(Comp_id, Depart_code, Depart_name, Num_order, Depart_status, Depart_remark, Depart_pid, Create_by, Create_time, Is_del)
            VALUES({depart.Comp_id}, '{depart.Depart_code}', '{depart.Depart_name}', '{depart.Num_order}', '{depart.Depart_status}', '{depart.Depart_remark}', '{depart.Depart_pid}', '{depart.Create_by}', '{depart.Create_time}', '{depart.Is_del}')";
            return myDbContext.Database.ExecuteSqlRaw(sql);
        }

        public int DelDepart(int id)
        {
            string sql = $"UPDATE dbo.Ment_depart SET Is_del=1 WHERE Depart_id={id}";
            return myDbContext.Database.ExecuteSqlRaw(sql);
        }

        public Ment_depart GetById(long id)
        {
            string sql = $"SELECT * FROM dbo.Ment_depart WHERE Is_del=0 AND Depart_id={id}";
            return myDbContext.Database.GetDbConnection().Query<Ment_depart>(sql).FirstOrDefault();
        }

        public Info_company GetByCompId(int id)
        {
            string sql = $"SELECT TOP 1 Comp_name FROM dbo.Info_company WHERE Is_del=0 ORDER BY Comp_id DESC";
            return myDbContext.Database.GetDbConnection().Query<Info_company>(sql).FirstOrDefault();
        }

        public int UpdDepart(Ment_depart d)
        {
            string sql = $@"UPDATE dbo.Ment_depart SET Comp_id={d.Comp_id}, Depart_code='{d.Depart_code}', Depart_name='{d.Depart_name}', Num_order={d.Num_order}, Depart_status={d.Depart_status}, Depart_remark='{d.Depart_remark}', Depart_pid={d.Depart_pid}, Update_by='{d.Update_by}', Update_time='{d.Update_time}'
            WHERE Depart_id={d.Depart_id}";
            return myDbContext.Database.ExecuteSqlRaw(sql);
        }
    }
}
